﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using FundSms.Client.IDAL;
using FundSms.Client.Model;
using FundSms.DBUtility;

namespace FundSms.Client.SqlDAL
{
    public class SmsReturn : ISmsReturn
    {
        #region ISmsReturn 成员
        private const string SQL_INSERT_RETURN = "INSERT INTO TSMS_RETURN_HUNDSUN(C_RETURN,C_MOBILE,C_CONTENT,C_PROPERTY,C_FROM,C_SMSNAME,C_SMSID,C_DATE,C_R1,C_R2,C_R3,C_R4,C_R5) VALUES (@C_RETURN,@C_MOBILE,@C_CONTENT,@C_PROPERTY,@C_FROM,@C_SMSNAME,@C_SMSID,@C_DATE,@C_R1,@C_R2,@C_R3,@C_R4,@C_R5)";
        private const string SQL_INSERT_RETURN_BATCH = "INSERT INTO TSMS_RETURN_HUNDSUN(C_RETURN,C_MOBILE,C_CONTENT,C_PROPERTY,C_FROM,C_SMSNAME,C_SMSID,C_DATE,C_R1,C_R2,C_R3,C_R4,C_R5) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}')";
        private const string SQL_UPDATE_RETURN = "UPDATE TSMS_RETURN_HUNDSUN SET C_RETURN=@STATUS WHERE C_SMSID=@C_SMSID and C_MOBILE=@C_MOBILE";
        private const string SQL_SELECT_COUNT_SENDCOUNT = "SELECT COUNT(1) FROM TSMS_RETURN_HUNDSUN WHERE c_returntime >= @RETURNTIME AND c_returntime < @NEXTRETURNTIME";
        private const string SQL_SELECT_COUNT_RETURNCOUNT = "SELECT COUNT(1) FROM TSMS_RETURN_HUNDSUN WHERE c_returntime >= @RETURNTIME AND c_returntime < @NEXTRETURNTIME  AND c_return <> ''";
        private const string SQL_SELECT_COUNT_UNRETURNCOUNT = "SELECT COUNT(1) FROM TSMS_RETURN_HUNDSUN WHERE c_returntime >= @RETURNTIME AND c_returntime < @NEXTRETURNTIME AND c_return = ''";
        private const string SQL_SELECT_COUNT_SUCCESSCOUNT = "SELECT COUNT(1) FROM TSMS_RETURN_HUNDSUN WHERE c_returntime >= @RETURNTIME AND c_returntime < @NEXTRETURNTIME AND c_return = '0'";
        private const string SQL_SELECT_COUNT_FAILURECOUNT = "SELECT COUNT(1) FROM TSMS_RETURN_HUNDSUN WHERE c_returntime >= @RETURNTIME AND c_returntime < @NEXTRETURNTIME AND c_return <> '0' AND c_return <> ''";

        private const string PARM_C_RETURN = "@C_RETURN";
        private const string PARM_C_MOBILENO = "@C_MOBILE";
        private const string PARM_C_CONTENT = "@C_CONTENT";
        private const string PARM_C_PROPERTY = "@C_PROPERTY";
        private const string PARM_C_FROM = "@C_FROM";
        private const string PARM_C_SMSNAME = "@C_SMSNAME";
        private const string PARM_C_SMSID = "@C_SMSID";
        private const string PARM_C_DATE = "@C_DATE";

        private const string PARM_RETURNTIME = "@RETURNTIME";
        private const string PARM_NEXTRETURNTIME = "@NEXTRETURNTIME";

        private const string PARM_C_STATUS = "@STATUS";

        private const string PARM_C_R1 = "@C_R1";
        private const string PARM_C_R2 = "@C_R2";
        private const string PARM_C_R3 = "@C_R3";
        private const string PARM_C_R4 = "@C_R4";
        private const string PARM_C_R5 = "@C_R5";

        public void Insert(TSMSReturnHundSunInfo returninfo)
        {
            SqlParameter[] logparms = GetReturnParameters();

            logparms[0].Value = returninfo.CReturn;
            logparms[1].Value = returninfo.CMobile;
            logparms[2].Value = returninfo.CContent;
            logparms[3].Value = returninfo.CProperty;
            logparms[4].Value = returninfo.CFrom;
            logparms[5].Value = returninfo.CSmsName;
            logparms[6].Value = returninfo.CSmsID;
            logparms[7].Value = returninfo.CDate;
            logparms[8].Value = returninfo.CR1;
            logparms[9].Value = returninfo.CR2;
            logparms[10].Value = returninfo.CR3;
            logparms[11].Value = returninfo.CR4;
            logparms[12].Value = returninfo.CR5;

            SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringClientSms, CommandType.Text, SQL_INSERT_RETURN, logparms);
        }

        public void InsertBatch(string sql)
        {
            SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringClientSms, CommandType.Text, sql, null);
        }

        public void InsertList(List<TSMSReturnHundSunInfo> li)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append("BEGIN");

            object[] obj = new object[11];

            int i = 1;

            foreach (TSMSReturnHundSunInfo loginfo in li)
            {
                obj[0] = loginfo.CReturn;
                obj[1] = loginfo.CMobile;
                obj[2] = loginfo.CContent;
                obj[3] = loginfo.CProperty;
                obj[4] = loginfo.CFrom;
                obj[5] = loginfo.CSmsName;
                obj[6] = loginfo.CSmsID;
                obj[7] = loginfo.CDate;
                obj[8] = loginfo.CR1;
                obj[9] = loginfo.CR2;
                obj[10] = loginfo.CR3;
                obj[11] = loginfo.CR4;
                obj[12] = loginfo.CR5;

                sb.Append(string.Format(SQL_INSERT_RETURN_BATCH, obj));
                sb.Append(";");

                i++;
            }

            sb.Append("END");

            SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringClientSms, CommandType.Text, sb.ToString(), null);
        }

        public void Update(TSMSReturnHundSunInfo returninfo)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public void UpdateStatus(string status, string smsid, string mobile)
        {
            SqlParameter[] logparms = GetReturnUpdateParameters();

            logparms[0].Value = status;
            logparms[1].Value = smsid;
            logparms[2].Value = mobile;

            SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringClientSms, CommandType.Text, SQL_UPDATE_RETURN, logparms);
        }

        /// <summary>
        /// 获得当前日期下的已发短信数量
        /// </summary>
        /// <param name="datetime"></param>
        /// <returns></returns>
        public int GetSendCount(DateTime datetime)
        {
            SqlParameter[] parms = new SqlParameter[2];

            parms[0] = new SqlParameter(PARM_RETURNTIME, SqlDbType.VarChar, 50);
            parms[1] = new SqlParameter(PARM_NEXTRETURNTIME, SqlDbType.VarChar, 50);

            parms[0].Value = datetime.ToString("yyyy-MM-dd");
            parms[1].Value = datetime.AddDays(1).ToString("yyyy-MM-dd");

            int result = (int)SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringClientSms, CommandType.Text, SQL_SELECT_COUNT_SENDCOUNT, parms);

            return result;
        }

        /// <summary>
        /// 获得指定日期下已经返回的短信数量
        /// </summary>
        /// <param name="datetime"></param>
        /// <returns></returns>
        public int GetReturnCount(DateTime datetime)
        {
            SqlParameter[] parms = new SqlParameter[2];

            parms[0] = new SqlParameter(PARM_RETURNTIME,SqlDbType.VarChar, 50);
            parms[1] = new SqlParameter(PARM_NEXTRETURNTIME, SqlDbType.VarChar, 50);

            parms[0].Value = datetime.ToString("yyyy-MM-dd");
            parms[1].Value = datetime.AddDays(1).ToString("yyyy-MM-dd");

            int result = (int)SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringClientSms, CommandType.Text, SQL_SELECT_COUNT_RETURNCOUNT, parms);

            return result;
        }

        /// <summary>
        /// 获得指定日期下未返回的短信数量
        /// </summary>
        /// <param name="datetime"></param>
        /// <returns></returns>
        public int GetUnReturnCount(DateTime datetime)
        {
            SqlParameter[] parms = new SqlParameter[2];

            parms[0] = new SqlParameter(PARM_RETURNTIME, SqlDbType.VarChar, 50);
            parms[1] = new SqlParameter(PARM_NEXTRETURNTIME, SqlDbType.VarChar, 50);

            parms[0].Value = datetime.ToString("yyyy-MM-dd");
            parms[1].Value = datetime.AddDays(1).ToString("yyyy-MM-dd");

            int result = (int)SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringClientSms, CommandType.Text, SQL_SELECT_COUNT_UNRETURNCOUNT, parms);

            return result;
        }

        /// <summary>
        /// 获得指定日期下成功的短信数量
        /// </summary>
        /// <param name="datetime"></param>
        /// <returns></returns>
        public int GetSuccessCount(DateTime datetime)
        {
            SqlParameter[] parms = new SqlParameter[2];

            parms[0] = new SqlParameter(PARM_RETURNTIME, SqlDbType.VarChar, 50);
            parms[1] = new SqlParameter(PARM_NEXTRETURNTIME, SqlDbType.VarChar, 50);

            parms[0].Value = datetime.ToString("yyyy-MM-dd");
            parms[1].Value = datetime.AddDays(1).ToString("yyyy-MM-dd");

            int result = (int)SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringClientSms, CommandType.Text, SQL_SELECT_COUNT_SUCCESSCOUNT, parms);

            return result;
        }

        /// <summary>
        /// 获得失败情况下的短信数量
        /// </summary>
        /// <param name="datetime"></param>
        /// <returns></returns>
        public int GetFailureCount(DateTime datetime)
        {
            SqlParameter[] parms = new SqlParameter[2];

            parms[0] = new SqlParameter(PARM_RETURNTIME, SqlDbType.VarChar, 50);
            parms[1] = new SqlParameter(PARM_NEXTRETURNTIME, SqlDbType.VarChar, 50);

            parms[0].Value = datetime.ToString("yyyy-MM-dd");
            parms[1].Value = datetime.AddDays(1).ToString("yyyy-MM-dd");

            int result = (int)SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringClientSms, CommandType.Text, SQL_SELECT_COUNT_FAILURECOUNT, parms);

            return result;
        }

        /// <summary>
        /// 获得缓存的参数
        /// </summary>
        /// <returns></returns>
        private static SqlParameter[] GetReturnParameters()
        {
            SqlParameter[] parms = SqlHelper.GetCachedParameters(SQL_INSERT_RETURN);

            if (parms == null)
            {
                parms = new SqlParameter[] {	
					new SqlParameter(PARM_C_RETURN, SqlDbType.Char, 1),						 
					new SqlParameter(PARM_C_MOBILENO, SqlDbType.VarChar, 13),
					new SqlParameter(PARM_C_CONTENT, SqlDbType.VarChar, 140),
					new SqlParameter(PARM_C_PROPERTY, SqlDbType.VarChar,2),
					new SqlParameter(PARM_C_FROM, SqlDbType.VarChar, 20),
					new SqlParameter(PARM_C_SMSNAME, SqlDbType.VarChar, 36),
					new SqlParameter(PARM_C_SMSID, SqlDbType.VarChar, 50),
					new SqlParameter(PARM_C_DATE, SqlDbType.VarChar, 50),
					new SqlParameter(PARM_C_R1, SqlDbType.VarChar, 50),
					new SqlParameter(PARM_C_R2, SqlDbType.VarChar, 50),
                    new SqlParameter(PARM_C_R3, SqlDbType.VarChar, 50),
                    new SqlParameter(PARM_C_R4, SqlDbType.VarChar, 50),
                    new SqlParameter(PARM_C_R5, SqlDbType.VarChar, 50)};

                SqlHelper.CacheParameters(SQL_INSERT_RETURN, parms);
            }

            return parms;
        }

        /// <summary>
        /// 获得缓存的参数
        /// </summary>
        /// <returns></returns>
        private static SqlParameter[] GetReturnUpdateParameters()
        {
            SqlParameter[] parms = SqlHelper.GetCachedParameters(SQL_UPDATE_RETURN);

            if (parms == null)
            {
                parms = new SqlParameter[] {												 
					new SqlParameter(PARM_C_STATUS, SqlDbType.Char, 1),
					new SqlParameter(PARM_C_SMSID, SqlDbType.VarChar, 50),
                    new SqlParameter(PARM_C_MOBILENO, SqlDbType.VarChar, 13)
            };

                SqlHelper.CacheParameters(SQL_UPDATE_RETURN, parms);
            }

            return parms;
        }

        #endregion
    }
}
